create  view vCashGiftTotals as
    select t.BT_ID as ID,
           t.TRANS_NUMBER,
           t.TRANSACTION_DATE,
           (sum(t.INVOICE_CREDITS)) as AMOUNT,
           max(p.SOURCE_CODE) as SOURCE_CODE
    from Trans  t, Invoice  i,Trans p
    where t.INVOICE_REFERENCE_NUM = i.REFERENCE_NUM and
          i.SOURCE_SYSTEM = 'FR' and
          p.TRANS_NUMBER = i.ORIGINATING_TRANS_NUM and
          p.LINE_NUMBER = 1 AND p.SUB_LINE_NUMBER = 1 and
          p.JOURNAL_TYPE = 'IN' and p.TRANSACTION_TYPE = 'DIST' and
         ((t.JOURNAL_TYPE = 'PAY' and t.TRANSACTION_TYPE = 'AR') or
               (t.JOURNAL_TYPE = 'IN' and t.TRANSACTION_TYPE = 'PAY'))
group by  t.BT_ID,t.TRANS_NUMBER,t.TRANSACTION_DATE

GO
GRANT REFERENCES ON  [dbo].[vCashGiftTotals] TO [IMIS]
GRANT SELECT ON  [dbo].[vCashGiftTotals] TO [IMIS]
GRANT INSERT ON  [dbo].[vCashGiftTotals] TO [IMIS]
GRANT DELETE ON  [dbo].[vCashGiftTotals] TO [IMIS]
GRANT UPDATE ON  [dbo].[vCashGiftTotals] TO [IMIS]
GO